﻿using aier.dataaccess.model;
using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Linq;

namespace aier.dataaccess.dapper
{

    public class StreetDal
    {
        public DapperHelper dapperHelper { get; set; }

        public ILog log { get; set; }

        /// <summary>
        /// 根据省份、市、区县的名称获取各自的guid
        /// </summary>
        /// <param name="provinceName"></param>
        /// <param name="cityName"></param>
        /// <param name="areaName"></param>
        /// <returns>item1：省份的guid，item2：市的guid，item3：区县的guid</returns>
        public Tuple<Guid?,Guid?,Guid?> GetPCAByName(string provinceName,string cityName,string areaName)
        {
            if (string.IsNullOrWhiteSpace(provinceName)|| string.IsNullOrWhiteSpace(cityName) ||string.IsNullOrWhiteSpace(areaName)) {
                return null;
            }
            var sql =
                    @"select top 1 new_provinceid from new_province where new_name =@provinceName
                      select top 1 new_cityid from new_city where  new_name =@cityName
                       select top 1 new_areaid from new_area where  new_name =@areaName
                    ";
            ProvinceView provinceView=null;
            CityView cityView=null;
            AreaView areaView=null;
            dapperHelper.ExecueWithoutTransaction(a =>
            {
                var multi = a.QueryMultiple(sql, new { provinceName = provinceName, cityName= cityName, areaName= areaName });
                provinceView = multi.Read<ProvinceView>().Single();
                cityView = multi.Read<CityView>().Single();
                var AreaViews = multi.Read<AreaView>();
                areaView = AreaViews.Count()==1? AreaViews.Single(): null;
            });
            return new Tuple<Guid?, Guid?, Guid?>(provinceView?.new_provinceId, cityView?.new_cityId, areaView?.new_areaId);
        }

        /// <summary>
        /// 通过视图获取所有街道信息
        /// </summary>
        /// <returns></returns>
        public List<StreetView> QueryStreetByProvinceCityArea(string provinceName, string cityName, string areaName, string hospital)
        {
            List<StreetView> streets = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
           

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select new_streetId,new_name,OwnerId,new_provinceName,new_cityName,new_areaName 
                               from AIER_STREET_INFO /**where**/ /**orderby**/";

                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("new_provinceName = @provinceName", new { provinceName = provinceName });
                if (!string.IsNullOrEmpty(cityName))
                {
                    sqlBuilder.Where("new_cityName = @cityName", new { cityName = cityName });
                }
                if (!string.IsNullOrEmpty(areaName))
                {
                    sqlBuilder.Where("new_areaName = @areaName", new { areaName = areaName });
                }
                if (!string.IsNullOrWhiteSpace(hospital))
                {
                    sqlBuilder.Where("new_hospital=@new_hospital", new { new_hospital = hospital });
                }
                sqlBuilder.OrderBy("CreatedOn desc");
                streets = a.Query<StreetView>(sqlTemplate.RawSql, sqlTemplate.Parameters).ToList();
            });

            return streets;
        }

        /// <summary>
        /// 根据街道ID查询视图信息
        /// </summary>
        /// <param name="shoolId"></param>
        /// <returns></returns>
        public StreetView QueryStreetId(string streetId)
        {
            StreetView streetView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_STREET_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                sqlBuilder.Where(" s.new_streetId=@new_streetId and s.statecode = 0 ");
                sqlParams.Add("@new_streetId", streetId);

                streetView = a.QueryFirstOrDefault<StreetView>(sqlTemplate.RawSql, sqlParams);
            });

            return streetView;
        }

        /// <summary>
        /// 根据街道名称查询街道视图信息
        /// </summary>
        /// <param name="communityId"></param>
        /// <returns></returns>
        public List<StreetView> QueryStreetByName(string streetName, string provinceName, string cityName, string areaName)
        {
            List<StreetView> streetView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_STREET_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);        
                sqlBuilder.Where("new_provinceName = @provinceName");
                if(!string.IsNullOrEmpty(cityName))
                {
                    sqlBuilder.Where("new_cityName = @cityName");
                    sqlParams.Add("@cityName", cityName);
                }
                if(!string.IsNullOrEmpty(areaName))
                {
                    sqlBuilder.Where("new_areaName = @areaName");
                    sqlParams.Add("@areaName", areaName);
                }
              
                sqlBuilder.Where("  CHARINDEX(@new_name,s.new_name)>0 and s.statecode = 0 ");
                sqlParams.Add("@new_name", streetName);
                sqlParams.Add("@provinceName", provinceName);
             
                streetView = a.Query<StreetView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return streetView;
        }

        /// <summary>
        /// 根据省市区/县ID查询街道视图信息
        /// </summary>
        /// <param name="provice"></param>
        /// <param name="city"></param>
        /// <param name="county"></param>
        /// <returns></returns>
        public List<StreetView> QueryStreetByProviceCityCounty(string provice,string city,string county)
        {
            List<StreetView> streetView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_STREET_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                sqlBuilder.Where("  s.new_province=@new_province and s.new_city=@new_city and s.new_area=@new_area and s.statecode=0 and s.statecode=0 ");
                sqlParams.Add("@new_province", provice);
                sqlParams.Add("@new_city", city);
                sqlParams.Add("@new_area", county);

                streetView = a.Query<StreetView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return streetView;
        }
        /// <summary>
        /// 根据区县查所属街道
        /// </summary>
        /// <param name="areaid">区县ID</param>
        /// <returns></returns>
        public List<StreetView> QueryStreetByAreaId(Guid areaid)
        {
            List<StreetView> streetViews = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from new_street /**where**/";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("new_area=@new_area");
                sqlParams.Add("@new_area", areaid);
                sqlBuilder.OrderBy("createon desc");
                streetViews = a.Query<StreetView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return streetViews;
        }
        /// <summary>
        /// 根据不同条件查询街道数量,只要属性不为空就视为查询条件
        /// 查询条件为：省份ID/市ID/区ID/街道ID/名称
        /// 原程序 LocationQueryServiceImpl/queryStreetCount
        /// </summary>
        /// <param name="streetView">街道对象</param>
        /// <returns></returns>
        public int QueryStreetCount(StreetView streetView)
        {
            int count = 0;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select count(*) from new_street s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                if (streetView.new_streetId != Guid.Empty)
                {
                    sqlBuilder.Where(" s.new_streetId=@new_streetId ");
                    sqlParams.Add("@new_streetId", streetView.new_streetId);
                }
                if (streetView.new_province != Guid.Empty)
                {
                    sqlBuilder.Where(" s.new_province=@new_province ");
                    sqlParams.Add("@new_province", streetView.new_province);
                }
                if (streetView.new_city != Guid.Empty)
                {
                    sqlBuilder.Where(" s.new_city=@new_city ");
                    sqlParams.Add("@new_city", streetView.new_city);
                }
                if (streetView.new_area != Guid.Empty)
                {
                    sqlBuilder.Where(" s.new_area=@new_area ");
                    sqlParams.Add("@new_area", streetView.new_area);
                }
                if (!string.IsNullOrWhiteSpace(streetView.new_name))
                {
                    sqlBuilder.Where(" s.new_name =@new_name ");
                    sqlParams.Add("@new_name", streetView.new_name);
                }
                log.Info(string.Format("StreetDal对像中,QueryStreetCount ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                count = a.ExecuteScalar<int>(sqlTemplate.RawSql, sqlParams);
            });
            return count;
        }
    }
}
